﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace TPCN.DAL
{
    public class Database
    {
        public static Database db = new Database();

        #region [Private fields]

        private string _ConnectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;

        public string ConnectionString
        {
            get { return _ConnectionString; }
            set { _ConnectionString = value; }
        }
        private SqlConnection _Connection;

        #endregion

        #region [Public property]

        public SqlConnection Connection
        {
            get { return _Connection; }
            set { _Connection = value; }
        }

        #endregion

        #region [Contructor]

        public Database()
        {
            if (_Connection == null)
                _Connection = new SqlConnection(_ConnectionString);
        }

        public Database(string _constr)
        {
            _ConnectionString = _constr;
        }

        #endregion

        #region [Public method]

        public SqlConnection GetConnection()
        {
            if (_Connection.State == System.Data.ConnectionState.Closed)
            {
                _Connection.Open();
                return _Connection;
            }
            else
            {
                return _Connection;
            }
        }

        public void CloseConnection()
        {
            if (_Connection != null && _Connection.State == ConnectionState.Open)
            {
                _Connection.Close();
            }
        }

        public DataTable GetData(SqlCommand cmd)
        {
            try
            {
                if (cmd.Connection == null)
                {
                    cmd.Connection = GetConnection();
                }

                using (DataSet dataset = new DataSet())
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter())
                    {
                        adapter.SelectCommand = cmd;
                        adapter.Fill(dataset);
                        return dataset.Tables[0];
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {

            }
        }

        public SqlDataReader GetDataToDataReader(SqlCommand cmd)
        {
            try
            {
                if (cmd.Connection == null)
                {
                    cmd.Connection = GetConnection();
                }

                return cmd.ExecuteReader();
            }
            finally
            {
                
            }
        }

        public DataTable GetData(string sql)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                cmd.Connection = GetConnection();
                using (DataSet dataset = new DataSet())
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter())
                    {
                        adapter.SelectCommand = cmd;
                        adapter.Fill(dataset);
                        CloseConnection();
                        return dataset.Tables[0];
                    }
                }
            }
        }

        public DataSet GetDataToDataSet(SqlCommand cmd)
        {
            try
            {
                if (cmd.Connection == null)
                {
                    cmd.Connection = GetConnection();
                }
                using (DataSet dataset = new DataSet())
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter())
                    {
                        adapter.SelectCommand = cmd;
                        adapter.Fill(dataset);
                        return dataset;
                    }
                }
            }
            finally
            {
                CloseConnection();
            }
        }

        public void ExecuteNonQuery(SqlCommand cmd)
        {
            try
            {
                cmd.Connection = GetConnection();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                CloseConnection();
            }
        }

        public object ExecuteScalar(SqlCommand cmd)
        {
            try
            {
                cmd.Connection = GetConnection();
                return cmd.ExecuteScalar();
            }
            finally
            {
                CloseConnection();
            }
        }

        public int DatabaseSize()
        {
            string sql = "SELECT SUM(size)*8*1024 FROM sysfiles";
            SqlCommand cmd = new SqlCommand(sql);
            cmd.CommandType = CommandType.Text;
            return (int)ExecuteScalar(cmd);
        }

        public bool IsConnected()
        {
            return GetData("SELECT GetDate()").Rows.Count > 0 ? true : false;
        }

        #endregion
    }
}
